Don’t use SQLRetrieveToFile and the other ODBC functions in the Xlodbc.xla add-in; use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead.
SQLRetrieveToFile retrieves all the results from a previously executed query and places them in a file.
To use this function, you must have established a connection with a data source by using SQLOpen, executed a query by using SQLExecQuery, and have the results of the query pending.
This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (Tools menu).
SQLRetrieveToFile(ConnectionNum, Destination, ColNamesLogical, ColumnDelimiter)
ConnectionNum Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery.
If ConnectionNum isn’t valid, SQLExecQuery returns Error 2015.
Destination Required. A string that specifies the name and path of the file where you want to place the results. If the file exists, its contents are replaced with the query results. If the file doesn’t exist, SQLRetrieveToFile creates and opens the file and fills it with the results.
The format of the data in the file is compatible with the Microsoft Excel .csv (comma-separated value) file format.
Columns are separated by the character specified by ColumnDelimiter, and the individual rows are separated by a carriage return.
If the file specified by Destination cannot be opened, SQLRetrieveToFile returns Error 2042.
ColNamesLogical Optional. True to have the column names be returned as the first row of data. False or omitted to have the column names not be returned.
ColumnDelimiter Optional. A string that specifies the character used to separate the elements in each row. For example, use "," to specify a comma delimiter, or use ";" to specify a semicolon delimiter. If you omit ColumnDelimiter, the list separator character is used.
If successful, SQLRetrieveToFile returns the query results, writes them to a file, and then returns the number of rows that were written to the file.
If SQLRetrieveToFile is unable to retrieve the results, it returns Error 2042 and doesn’t write the file.
If there are no pending results on the connection, SQLRetrieveToFile returns Error 2042.
Before calling SQLRetrieveToFile, you must do the following:
This example runs a query on the Northwind database. The result of the query, which is a list of all products that are currently on order, is written as the delimited text file Output.txt in the current folder.
databaseName = "Northwind"
queryString = _
"SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
SQLRetrieveToFile chan, "OUTPUT.TXT", True
SQLClose chan